<?php
namespace Sevstudio;

class MySQLHelper{
	public $SucBackUpFilePaths; //记录备份成功的文件名集合
	//构造函数
	function __construct($param){
		$this->Host = empty($param['host']) ? 'localhost' : $param['host'];
		$this->Port = empty($param['port']) ? 3306 : intval($param['port']);
		$this->Uid = empty($param['uid']) ? '' : $param['uid'];
		$this->Pwd = empty($param['pwd']) ? '' : $param['pwd'];
		$this->Databases = empty($param['databases']) ? '' : $param['databases'];
		$this->Charset = empty($param['charset']) ? 'utf8' : $param['charset'];
		$this->FileExtention = empty($param['extention']) ? '.sql' : $param['extention']; //备份文件后缀名 .开头
		$this->UseSection = !isset($param['usesection']) ? false : $param['usesection']; //默认不使用分卷
		$this->SectionSize = !isset($param['sectionsize']) ? 5*1024*1024 : floatval($param['sectionsize']); //分卷大小，默认5M
		//参数校验
		if($this->Uid == ''){ $this->throwError('uid was needed'); }
		if($this->Databases == ''){ $this->throwError('databases was needed'); }
		if(!is_bool($this->UseSection)){ $this->throwError('usesection must be boolean'); }
		
        $this->init();
	}
	//初始化
	private function init(){
		//规范备份文件后缀名
		if(substr($this->FileExtention,0,1) != '.'){
			$this->FileExtention = '.'.$this->FileExtention;
		}
		$this->BackFileName = 'data_'.date("YmdHis",time()).rand(1000,9999); //备份文件名，不含分卷和后缀
		//分卷信息初始化
		$this->SectionIndex = 1;
		$this->PageCount = 0; //累计单个备份文件写入语句的次数
		$this->PageStep = 5; //单个文件每写入N次语句则开始判断分卷大小
		
        if(extension_loaded('pdo') && extension_loaded('pdo_mysql')){
            define("SEVSTUDIO_BACK_METHOD","PDO");
        }else if(extension_loaded('mysqli')){
            define("SEVSTUDIO_BACK_METHOD","MYSQLI");
        }else if(extension_loaded('mysql')){
            define("SEVSTUDIO_BACK_METHOD","MYSQL");
        }else{
            $this->throwError('No backup plug-ins are available');
        }
	}
	//错误处理
	private function throwError($msg){
		die($msg);
	}
	//连接数据库
	public function connect(){
		try{
		    if(SEVSTUDIO_BACK_METHOD == 'PDO') {
                $this->Con = new \PDO(
                    "mysql:host={$this->Host};port={$this->Port};dbname={$this->Databases}",
                    $this->Uid,
                    $this->Pwd,
                    array(\PDO::MYSQL_ATTR_INIT_COMMAND => "set names '{$this->Charset}'")
                );
            }else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
                $this->Con = mysqli_connect($this->Host,$this->Uid,$this->Pwd,$this->Databases,$this->Port);
                if(!$this->Con){ $this->throwError('MySQL connected failed'); }
                mysqli_query($this->Con,"set names '{$this->Charset}'");
            }else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
                $this->Con = mysql_connect($this->Host.':'.$this->Port,$this->Uid,$this->Pwd);
                if($this->Con === false){ $this->throwError('MySQL connected failed'); }
                if(mysql_select_db($this->Databases,$this->Con) === false) {
                    $this->throwError('select databases failed');
                }
                if(mysql_query("set names '{$this->Charset}'") === false){
                    $this->throwError('MySQL connected failed');
                }
            }
		}catch(\Exception $e){
			$this->throwError('MySQL connected failed');
		}
	}
	
	//获取数据库版本
	public function GetVersion(){
		if(empty($this->Con)){ $this->connect(); }
		$version = '';
		$sql = 'select version()';
		if(SEVSTUDIO_BACK_METHOD == 'PDO') {
            $result = $this->Con->query($sql);
			$version = $result->fetchColumn();
        }else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
            $result = mysqli_query($this->Con,$sql);
			$tmp = mysqli_fetch_array($result);
			$version = $tmp[0];
        }else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
            $result = mysql_query($sql,$this->Con);
			$tmp = mysql_fetch_array($result);
			$version = $tmp[0];
        }
		return $version;
	}
	/*
	 *执行备份入口
	 *dirPath string 备份文件保存路径(文件夹)
	 *return boolean 失败：false，成功：true
	 */
	public function BackUp($dirPath = 'backup'){
        //备份文件夹处理
	    $_dirpath = $this->fixPath($dirPath);
        if(!file_exists($_dirpath)){
            if(!mkdir($_dirpath,0777,true)){ $this->throwError('make save directory failed'); }
        }
        if(substr($_dirpath,strlen($_dirpath)-1) == DIRECTORY_SEPARATOR){
            $this->BackUpSavePath = substr($_dirpath,0,strlen($_dirpath)-1);
        }else{
            $this->BackUpSavePath = $_dirpath;
        }
        $this->connect();
        if(SEVSTUDIO_BACK_METHOD == 'PDO') {
            return $this->BackUp_PDO($dirPath);
        }else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
            return $this->BackUp_MySQLi($dirPath);
        }else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
            return $this->BackUp_MySQL($dirPath);
        }
	}
	
	///PDO方式备份
	private function BackUp_PDO(){
        $result = $this->Con->query('show tables');
        if($result === false){ return false; }
        $content = "set names '{$this->Charset}';".PHP_EOL;
        $this->SaveBackFile($content);//写出
        //表名循环
        while($table = $result->fetchColumn()){
            //表结构
            $content.= PHP_EOL . "DROP TABLE IF EXISTS `{$table}`;".PHP_EOL;
            $tableCreate = $this->Con->query("show create table `$table`");
            $content.= $tableCreate->fetchColumn(1).';'.PHP_EOL . PHP_EOL;
            $this->SaveBackFile($content);//写出
            //表数据
            $data = $this->Con->query("select * from `$table`");
            while($row = $data->fetch(\PDO::FETCH_ASSOC)){
                $content.= $this->GetOneInsertQuery($table,$row);
                $this->SaveBackFile($content);//写出
            }
        }
        return true;
    }
    
	//MySQLi方式备份
    private function BackUp_MySQLi(){
        $result = mysqli_query($this->Con,'show tables');
        if($result === false){ return false; }
        $content = "set names '{$this->Charset}';".PHP_EOL;
        $this->SaveBackFile($content);//写出
        //表名循环
        while($tableInfo = mysqli_fetch_array($result)){
            $table = $tableInfo[0];
            //表结构
            $content.= PHP_EOL . "DROP TABLE IF EXISTS `{$table}`;".PHP_EOL;
            $tableCreate = mysqli_query($this->Con,"show create table `$table`");
            $tableCreate = mysqli_fetch_assoc($tableCreate);
            $content.= $tableCreate['Create Table'].';'.PHP_EOL . PHP_EOL;
            $this->SaveBackFile($content);//写出
            //表数据
            $data = mysqli_query($this->Con,"select * from `$table`");
            while($row = mysqli_fetch_assoc($data)){
                $content.= $this->GetOneInsertQuery($table,$row);
                $this->SaveBackFile($content);//写出
            }
        }
        return true;
    }
    //MySQL方式备份
    private function BackUp_MySQL(){
        $result = mysql_query('show tables',$this->Con);
        if($result === false){ return false; }
        $content = "set names '{$this->Charset}';".PHP_EOL;
        $this->SaveBackFile($content);//写出
        //表名循环
        while($tableInfo = mysql_fetch_array($result)){
            $table = $tableInfo[0];
            //表结构
            $content.= PHP_EOL . "DROP TABLE IF EXISTS `{$table}`;".PHP_EOL;
            $tableCreate = mysql_query("show create table `$table`",$this->Con);
            $tableCreate = mysql_fetch_array($tableCreate);
            $content.= $tableCreate['Create Table'].';'.PHP_EOL . PHP_EOL;
            $this->SaveBackFile($content);//写出
            //表数据
            $data = mysql_query("select * from `$table`",$this->Con);
            while($row = mysql_fetch_assoc($data)){
                $content.= $this->GetOneInsertQuery($table,$row);
                $this->SaveBackFile($content);//写出
            }
        }
        return true;
    }
    //获取一条数据的insert语句
    private function GetOneInsertQuery($tableName,$row){
        $keys = array_keys($row);
        $keys = array_map('addslashes',$keys);
        $keys = join('`,`',$keys);
        $keys = "`".$keys."`";
        $vals = array_values($row);
        $vals = array_map('addslashes',$vals);
        $vals = join("','",$vals);
        $vals = "'".$vals."'";
        return "INSERT INTO `$tableName`($keys) VALUES($vals);".PHP_EOL;
    }
	//保存备份数据到文件
	private function SaveBackFile(&$content){
		$fileName = $this->BackUpSavePath. DIRECTORY_SEPARATOR .$this->BackFileName.'_'.$this->SectionIndex.$this->FileExtention;
		file_put_contents($fileName,$content,FILE_APPEND);
		$content = '';
		$this->PageCount++;
		$this->SucBackUpFilePaths[] = $this->BackFileName.'_'.$this->SectionIndex;
		//拒绝每写入一次语句就判断一次分卷大小
		if($this->UseSection  && $this->PageCount >= $this->PageStep){
			$this->PageCount = 0;
			try{
				$size = filesize($fileName);
				if($size > $this->SectionSize){
					$this->SectionIndex++;
				}
			}catch(\Exception $e){
				//
			}
			clearstatcache();
		}
	}
	//路径标准化
	private function fixPath($str){
		$t = str_replace('\\',DIRECTORY_SEPARATOR,$str);
		return str_replace('/',DIRECTORY_SEPARATOR,$t);
	}
	/*
	 *执行还原入口
	 *filepath string 还原文件路径或文件夹路径
	 *deletefilewhilesuccess boolean 备份成功后是否删除备份文件
	 *return 失败：false，成功：执行成功及失败语句数量
	 */
	public function Restore($filepath = 'backup',$deletefilewhilesuccess = false){
		$_filepath = $this->fixPath($filepath);
		if(!file_exists($_filepath)){ return false; }
		
		$this->RestoreResult = array(
			'success' => 0, //成功执行命令数
			'error' => 0, //失败...
		);		
		if(is_dir($_filepath)){
			//还原目录
			//去掉末尾路径分隔符
			if(substr($_filepath,strlen($_filepath)-1) == DIRECTORY_SEPARATOR){
				$_filepath = substr($_filepath,0,strlen($_filepath)-1);
			}
			//读取目录下.sql文件
			$files = array();
			$hd = opendir($_filepath);
			while($o = readdir($hd)){
				if($o == '.' || $o == '..' || strpos($o,'.') === false){ continue; }
				$type = substr($o,strripos($o,'.'));
				if(strtolower($type) != $this->FileExtention){ continue; }
				$files[] = $o;
			}
			closedir($hd);
			if(count($files) < 1){ return false; }
			//开始处理
			if($this->UseSection){
				//使用分卷
				$tmpstr = implode('-',$files);
				$result = preg_match_all('/data_[a-z0-9]{30,}_\d{1,}\\'.$this->FileExtention.'/i',$tmpstr,$arr,PREG_PATTERN_ORDER);
				if($result === false || count($arr) < 1){
					return false;//没有符合分卷规则的备份
				}
				//索引排序，导入时需要按顺序导入
				$real = array();
				$rd = ''; //随机标记统一，多个随机标记代表执行了多次备份的文件放到了一起。
				foreach($arr[0] as $o){
					$info = explode('_',$o);
					$rand = $info[1];
					if($rd == ''){
						$rd = $rand;
					}else if($rd != $rand){
						/*存在多个随机标记
						代表执行了多次备份的文件放到了一起
						或其他数据库的备份文件也在一起
						*/
						$this->throwError('Backup files with different databases');
					}
					$index = str_replace($this->FileExtention,'',$info[2]);
					$real[$index] = $o;
				}
				foreach($real as $x){
					$this->RestoreSingle($_filepath . DIRECTORY_SEPARATOR . $x);
				}
			}else{
				//不使用分卷，所有.sql文件
				foreach($files as $o){
					$this->RestoreSingle($_filepath . DIRECTORY_SEPARATOR . $o);
				}
			}
		}else{
			//还原单个文件
			$this->RestoreSingle($_filepath);
		}
		if($deletefilewhilesuccess && !empty($this->SuccessFiles)){
			foreach($this->SuccessFiles as $o){
				@unlink($o);
			}
		}
		return $this->RestoreResult;
	}
	//还原一个分卷
	private function RestoreSingle($filename){
		$this->connect();
		$content = file_get_contents($filename);
        $rows = explode(';'.PHP_EOL,$content);
        foreach($rows as $r){
            if(trim($r) == ''){ continue; }
            $res = $this->Query($r);
            if($res === false){
                $this->RestoreResult['error']++;
            }else{
                $this->RestoreResult['success']++;
            }
        }
		if($this->RestoreResult['error'] < 1){
			//成功时，记录成功文件路径，用于删除备份文件
			$this->SuccessFiles[] = $filename;
		}
	}
    //执行query命令
	private function Query($sql){
        if(SEVSTUDIO_BACK_METHOD == 'PDO') {
            return $this->Con->query($sql);
        }else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
            return mysqli_query($this->Con,$sql);
        }else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
            return mysql_query($sql,$this->Con);
        }
    }
}